import datetime
from typing import Optional, Annotated
from sqlalchemy import String, DateTime, ForeignKey, func
from sqlalchemy.orm import DeclarativeBase, relationship, mapped_column, Mapped
from sqlalchemy.ext.associationproxy import AssociationProxy, association_proxy

str_50 = Annotated[str, 50]
str_200 = Annotated[str, 200]
int_pk = Annotated[int, mapped_column(primary_key=True)]


class Base(DeclarativeBase):
    pass


class Book(Base):
    __tablename__ = 'books'
    id: Mapped[int_pk]
    title: Mapped[str_200]
    count: Mapped[int] = mapped_column(default=1)
    release_date: Mapped[datetime.date] = mapped_column(nullable=False)
    author: Mapped["Author"] = relationship(
            back_populates="books",
            cascade="merge", 
            uselist=False,
            lazy="selectin"
            )
    author_id = mapped_column(ForeignKey("authors.id"))


class Author(Base):
    __tablename__ = 'authors'
    id: Mapped[int_pk]
    first_name: Mapped[str_50]
    last_name: Mapped[str_50]


class Student(Base):
    __tablename__ = 'students'
    id: Mapped[int_pk]
    first_name: Mapped[str_50]
    last_name: Mapped[str_50]
    phone: Mapped[int]
    email: Mapped[str_50]
    average_score: Mapped[float]
    scholarship: Mapped[bool] = mapped_column(default=True)
    
    receiving_books: AssociationProxy[list["ReceivingBook"]] = association_proxy(
            "student_receiving_books", "books"
            )


class ReceivingBook(Base):
    __tablename__ = "receiving_book"

    book_id: Mapped[int_pk] = mapped_column(ForeignKey('books.id'))
    student_id: Mapped[int_pk] = mapped_column(ForeignKey('students.id'))
    date_of_issue: Mapped[datetime.date]
    date_of_return: Mapped[datetime.date]
    
    student = relationship(
            Student, 
            backref="student_receiving_books",
            cascade="all",
            )
    book = relationship(Book)
